﻿/*                              COMMON FUNCTIONS IN USER MANAGER                        */

#region NameSpace
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data.SqlClient ;
using System.Text;
using  Microsoft.CSharp;
using System.Globalization;
using System.IO;
using System.Collections;
using CommonFunctions;
#endregion

namespace UserManage
{
#region  "Struct"   
   public  struct UserDetails
    {
        string _userID;

        public string UserID
        {
            get { return _userID; }
            set { _userID = value; }
        }

        string _userFirstName;

        public string UserFirstName
        {
            get { return _userFirstName; }
            set { _userFirstName = value; }
        }
        string _userMiddleName;

        public string UserMiddleName
        {
            get { return _userMiddleName; }
            set { _userMiddleName = value; }
        }
        string _userLastName;

        public string UserLastName
        {
            get { return _userLastName; }
            set { _userLastName = value; }
        }
        string _userNickName;

        public string UserNickName
        {
            get { return _userNickName; }
            set { _userNickName = value; }
        }
        string _gender;

        public string Gender
        {
            get { return _gender; }
            set { _gender = value; }
        }
        string  _dob;

        public string  Dob
        {
            get { return _dob; }
            set { _dob = value; }
        }
        string _address;

        public string Address
        {
            get { return _address; }
            set { _address = value; }
        }
        string _phone;

        public string Phone
        {
            get { return _phone; }
            set { _phone = value; }
        }
        string _personalInformation;

        public string PersonalInformation
        {
            get { return _personalInformation; }
            set { _personalInformation = value; }
        }
        string _ssn;

        public string SSN
        {
            get { return _ssn; }
            set { _ssn = value; }
        }
        string _authenticationQuestion;

        public string AuthenticationQuestion
        {
            get { return _authenticationQuestion; }
            set { _authenticationQuestion = value; }
        }
        string _authenticationAnswer;

        public string AuthenticationAnswer
        {
            get { return _authenticationAnswer; }
            set { _authenticationAnswer = value; }
        }
        string _password;

        public string Password
        {
            get { return _password; }
            set { _password = value; }
        }
       string _groupID;

       public string GroupID
       {
           get { return _groupID; }
           set { _groupID = value; }
       }
       string _groupName;

       public string GroupName
       {
           get { return _groupName; }
           set { _groupName = value; }
       }
       string _groupDescription;

       public string GroupDescription
       {
           get { return _groupDescription; }
           set { _groupDescription = value; }
       }
       string _createdBy;

       public string CreatedBy
       {
           get { return _createdBy; }
           set { _createdBy = value; }
       }
       string _dateOfCreation;

       public string DateOfCreation
       {
           get { return _dateOfCreation; }
           set { _dateOfCreation = value; }
       }
    }

   public struct SearchCriteria
   {
       string _setCriteria;

       public string SetCriteria
       {
           get { return _setCriteria; }
           set { _setCriteria = value; }
       }

       string _criteriaName;

       public string CriteriaName
       {
           get { return _criteriaName; }
           set { _criteriaName = value; }
       }

       string _role;

       public string Role
       {
           get { return _role; }
           set { _role = value; }
       }

       string _dateofCreation;

       public string DateofCreation
       {
           get { return _dateofCreation; }
           set { _dateofCreation = value; }
       }

       string _dateStatus;

       public string DateStatus
       {
           get { return _dateStatus; }
           set {_dateStatus=value ;}
       }

   }

   public struct UserRoles
   {
       string _roleID;
       public string RoleID
       {
           get { return _roleID; }
           set { _roleID = value; }
       }

       string _roleName;
       public string RoleName
       {
           get { return _roleName; }
           set { _roleName = value; }
       }

       string _roleDescription;
       public string RoleDescription
       {
           get { return _roleDescription; }
           set { _roleDescription = value; }
       }
   }

   public struct UserPermission
   {
       string _permissionID;
       public string PermissionID
       {
           get 
           {
               return _permissionID;
           }
           set
           {
               _permissionID = value;
           }
       }

       string _permissionName;
       public string PermissionName
       {
           get
           {
               return _permissionName;
           }
           set
           {
               _permissionName = value;
           }
       }

       string _customID;
       public string CustomID
       {
           get
           {
               return _customID;
           }
           set
           {
               _customID = value;
           }
       }

       string _actorType;
       public string ActorType
       {
           get
           {
               return _actorType;
           }
           set
           {
               _actorType = value;
           }
       }

       string _actorID;
       public string ActorID
       {
           get
           {
               return _actorID;
           }
           set
           {
               _actorID = value;
           }
       }

       string _utilityID;
       public string UtilityID
       {
           get
           {
               return _utilityID;
           }
           set
           {
               _utilityID = value;
           }
       }

       string _permissionActive;
       public string PermissionActive
       {
           get
           {
               return _permissionActive;
           }
           set
           {
               _permissionActive = value;
           }
       }
   }

   public struct Utility
   {
       string _groupID;
       public string GroupID
       {
           get
           {
               return _groupID;
           }
           set
           {
               _groupID = value;
           }
       }

       string _groupName;
       public string GroupName
       {
           get
           {
               return _groupName;
           }
           set 
           {
               _groupName = value;
           }
       }

       string _groupDescription;
       public string GroupDescription
       {
           get
           {
               return _groupDescription;
           }
           set
           {
               _groupDescription = value;
           }
       }

       string _createdBy;
       public string CreatedBy
       {
           get
           {
               return _createdBy;
           }
           set
           {
               _createdBy=value;
           }
       }


       string _dateOfCreation;
       public string DateOfCreation
       {
           get
           {
               return _dateOfCreation;
           }
           set
           {
               _dateOfCreation = value;
           }
       }

       string _utilityID;
       public string UtilityID
       {
           get
           {
               return _utilityID;
           }
           set
           {
               _utilityID = value;
           }
       }

       string _utilityName;
       public string UtilityName
       {
           get 
           {
               return _utilityName;
           }
           set
           {
               _utilityName =value;
           }
       }

   }

#endregion


/// <summary>
/// Summary description for UserManager
/// </summary>
public class UserManager
{

    //static string connectionString = ConfigurationManager.AppSettings["ConnectionString"];

    #region SQLConnection

   // static string connectionString = "Data Source=198.64.249.24,1092;Initial Catalog=eInternShip;User Id=bmjobmjo;Password=goodhopealways";// ConfigurationManager.ConnectionStrings["connectUserManager"].ToString();

    static string connectionString = ConfigurationManager.ConnectionStrings["connectUserManager"].ToString();
    private static SqlConnection _sqlcon = new SqlConnection(connectionString);

    SqlCommand _sqlcom = new SqlCommand("", _sqlcon);
    public SqlCommand Sqlcom
    {
        get
        {
            return _sqlcom;
        }
        set
        {
            _sqlcom = value;
        }
    }

    static SqlDataAdapter _sqlada = new SqlDataAdapter("", _sqlcon);
    public static SqlDataAdapter SqlAda
    {
        get
        {
            return _sqlada;
        }
        set
        {
            _sqlada = value;
        }
    }

    SqlCommandBuilder _sqlcomb = new SqlCommandBuilder(SqlAda);
    public SqlCommandBuilder Sqlcomb
    {
        get
        {
            return _sqlcomb;
        }
        set
        {
            _sqlcomb = value;
        }
    }



    DataSet _ds = new DataSet();
    public DataSet Ds
    {
        get
        {
            return _ds;
        }
        set
        {
            _ds = value;
        }
    }

    DataRow _dr;
    public DataRow Dr
    {
        get
        {
            return _dr;
        }
        set
        {
            _dr = value;
        }
    }
    #endregion

   #region "General Functions"

   public UserManager()
	{
		//
		// TODO: Add constructor logic here
		//
	}

    
    public DataSet GetData(string table, string condition)
    {

        if (condition == string.Empty)
        {
            SqlAda.SelectCommand.CommandText = "select * from " + table + "";
        }
        else
        {
            SqlAda.SelectCommand.CommandText = "select * from " + table + " where " + condition + " ";
        }

        _ds = new DataSet();
        SqlAda.Fill(_ds);
        return _ds;
    }

    public DataSet GetData(string table1,string table2, string condition)
    {
        SqlAda.SelectCommand.CommandText = "select T1.*,T2.* from " + table1 + " as T1," + table2 + " as T2 where " + condition + " ";
        //}

        _ds = new DataSet();
        SqlAda.Fill(_ds);
        return _ds;
    }

    /// <summary>
    /// Validate number.
    /// </summary>
    /// <param name="number"></param>
    /// <remarks>
    /// Check whether tha value is a number or not.While attempting to convert it to int,if it is not a number
    /// exception occurs and the function will return false.Otherwise return true.
    /// </remarks>
    /// <returns></returns>
    public bool CheckNumber(string number)
    {
        try
        {
            Convert.ToInt64(number);
            return true;
        }
        catch (Exception exp)
        {
            return false;
        }

    }


    public string UserNameAvailabilityCheck(string userName)
    {
        #region Fields
        string query = string.Empty;
        string name=string.Empty;
        #endregion

        CommonFunctions.CommonClasses commonClass = new CommonClasses();
        query = "SELECT     UL_UserName FROM  UserList where UL_UserName='"+userName+"'";
        object user = commonClass.ExecuteScalar(query);
        return Convert.ToString(user);

    }

   #endregion

    #region "User"

    /// <summary>
    /// Add the details of a new user
    /// </summary>
    /// <param name="user"></param>
    /// <remarks>
    /// Personal information of users are stored 
    /// in the database using this function
    /// </remarks>
    /// <returns>boolean </returns>

    public bool AddUser(UserDetails user)
    {

        SqlAda.SelectCommand.CommandText = "select * from UserDetails  where UD_FirstName = '" + user.UserFirstName + "'  and UD_LastName = '" + user.UserLastName + "' and UD_Address = '" + user.Address + "'";
        SqlAda.Fill(Ds);
        if (Ds.Tables[0].Rows.Count > 0)
        {
            return false;
        }
        else
        {           
            Dr = Ds.Tables[0].NewRow();
            Dr["UD_FirstName"] = user.UserFirstName;

            if (user.UserMiddleName != string.Empty)
                Dr["UD_MiddleName"] = user.UserMiddleName;

            Dr["UD_LastName"] = user.UserLastName;

            if (user.UserNickName != string.Empty)
                Dr["UD_NickName"] = user.UserNickName;
            if (user.Gender != string.Empty)
                Dr["UD_Gender"] = user.Gender;
            if (user.Dob == string.Empty)
                Dr["UD_DateOfBirth"] = DBNull.Value;
            else
                Dr["UD_DateOfBirth"] = user.Dob;
            if (user.Address != string.Empty)
                Dr["UD_Address"] = user.Address;
            if (user.Phone != string.Empty)
                Dr["UD_Phone"] = user.Phone;
            if (user.PersonalInformation != string.Empty)
                Dr["UD_PersonalInformation"] = user.PersonalInformation;
            if (user.SSN != string.Empty)
                Dr["UD_SSN"] = user.SSN;

            Ds.Tables[0].Rows.Add(Dr);
            SqlAda.Update(Ds);
        }

        return true;
    }


    /// <summary>
    /// Delete existing user
    /// </summary>
    /// <param name="user"></param>
    /// <returns>boolean</returns>
    /// 
    public bool DeleteUser(string conditionDeleteRolesMap, string conditionDeletePermissionMap, string conditionDeleteUserList, string conditionDeleteUser)
    {
        Sqlcom.CommandText = "delete from UserRolesMap where " + conditionDeleteRolesMap + "";
        _sqlcon.Open();
        Sqlcom.ExecuteNonQuery();
        _sqlcon.Close();

        Sqlcom.CommandText = "delete from PermissionsMap where " + conditionDeletePermissionMap + "";
        _sqlcon.Open();
        Sqlcom.ExecuteNonQuery();
        _sqlcon.Close();

        Sqlcom.CommandText = "delete from UserList where " + conditionDeleteUserList + "";
        _sqlcon.Open();
        Sqlcom.ExecuteNonQuery();
        _sqlcon.Close();

        Sqlcom.CommandText = "delete from UserDetails where " + conditionDeleteUser + "";
        _sqlcon.Open();
        Sqlcom.ExecuteNonQuery();
        _sqlcon.Close();

        return true;
    }

    /// <summary>
    /// Edit User Details
    /// </summary>
    /// <param name="user"></param>
    /// <remarks>
    /// Details of a user such as address,phone number etc.are subjected to changes.
    /// Sometimes additional information is required.
    /// </remarks>
    /// <returns>boolean</returns>
    public bool EditUser(UserDetails user)
    {
        string dateOfBirth;
        if (user.Dob == string.Empty)
            dateOfBirth = "UD_DateOfBirth=null,";
        else
            dateOfBirth = "UD_DateOfBirth='" + user.Dob + "',";

        //_sqlcom = new SqlCommand("", _sqlcon);
        Sqlcom.CommandText = "update UserDetails set UD_FirstName='" + user.UserFirstName + "'," +
        "UD_MiddleName='" + user.UserMiddleName + "',UD_LastName='" + user.UserLastName + "',UD_NickName" +
        "='" + user.UserNickName + "',UD_Gender='" + user.Gender + "'," + dateOfBirth + " " +
        "UD_Address='" + user.Address + "',UD_Phone='" + user.Phone + "',UD_PersonalInformation='" + user.PersonalInformation + "'," +
        "UD_SSN='" + user.SSN + "' where UD_UserPersonalID='" + user.UserID + "'";
        _sqlcon.Open();
        Sqlcom.ExecuteNonQuery();
        _sqlcon.Close();
        return true;
    }
    
    /// <summary>
    /// search a user
    /// </summary>
    /// <param name="toSearch"></param>
    /// <param name="result"></param>
    /// <remarks>
    /// Users can be searched.This can be performed even by typing the starting letter of their name,
    /// the matching names and their details are listed as a report.
    /// </remarks>
    /// <returns>boolean</returns>
    public bool SearchUser(string toSearch, out DataSet result)
    {
        result = new DataSet();
        try
        {          
            SqlAda.SelectCommand.CommandText = "select (UD.UD_FirstName+' '+IsNull(UD.UD_MiddleName,'')+' '+IsNull(UD.UD_LastName,'')) as EmployeeName,UD.*,UL.* from UserDetails as UD LEFT OUTER JOIN UserList as UL ON " +
                           "UD.UD_UserPersonalID=UL.UL_UserPersonalID   where UD.UD_FirstName " +
                           "like '" + '%' + "' + '" + toSearch + "' + '" + '%' + "' or UD.UD_MiddleName like '" + '%' + "' +'" + toSearch + "' + '" + '%' + "' or UD.UD_LastName like '" + '%' + "' + '" + toSearch + "' + '" + '%' + "' order by EmployeeName";

           
            if (toSearch == "Latest")
            {
                SqlAda.SelectCommand.CommandText = "select top 10 (ud.UD_FirstName+' '+IsNull(ud.UD_MiddleName,'')+' '+IsNull(ud.UD_LastName,'')) as EmployeeName,ud.UD_Phone,ud.UD_Gender,ud.UD_Photo,ud.UD_UserPersonalID,ul.* " +
                "from userdetails as ud left outer join userlist as ul on  ud.ud_userpersonalID=ul.ul_userpersonalID  " +
                " order by UD_userPersonalID desc";
            }
            SqlAda.Fill(result);
            return true;
        }
        catch (Exception ex)
        {
            return false;
        }
    
    }

    /// <summary>
    /// include additional search options
    /// </summary>
    /// <param name="criteria"></param>
    /// <param name="result"></param>
    /// <remarks>
    /// User can be searched using their firstname or lastname and role or loginID.
    /// They can also be searched on the basis of the date on which they are created
    /// or those who are created before or after the specified date .
    /// </remarks>
    /// <returns></returns>
  public bool AdvancedUserSearch(SearchCriteria criteria, out DataSet result) 
  {
      result = new DataSet();      
      string dateOfCreation = "";
      string userName = "";
     
      if (criteria.SetCriteria == "FirstName")
      {
          if (criteria.Role == "Select") //if no role is selected

              SqlAda.SelectCommand.CommandText = "select (UD.UD_FirstName+' '+IsNull(UD.UD_MiddleName,'')+' '+IsNull(UD.UD_LastName,'')) as EmployeeName,UD.*,UL.* from UserDetails as UD LEFT OUTER JOIN UserList as UL ON " +
              "UD.UD_UserPersonalID=UL.UL_UserPersonalID WHERE UD.UD_FirstName LIKE '" + '%' + "' + '" + criteria.CriteriaName + "' + '" + '%' + "' order by EmployeeName";
          else
              SqlAda.SelectCommand.CommandText = "SELECT     (UD.UD_FirstName+' '+IsNull(UD.UD_MiddleName,'')+' '+IsNull(UD.UD_LastName,'')) as EmployeeName,UD.*, UL.* FROM UserDetails AS UD JOIN UserRolesMap AS UR " +
                        "ON UD.UD_UserPersonalID = UR.UM_UserID LEFT OUTER JOIN UserList AS UL ON UD.UD_UserPersonalID = UL.UL_UserPersonalID " +
                        "WHERE UD.UD_FirstName LIKE '" + '%' + "' + '" + criteria.CriteriaName + "' + '" + '%' + "' AND UR.UM_RoleID = '" + criteria.Role + "' order by EmployeeName";
      }
      else if (criteria.SetCriteria == "LastName")
      {
          if (criteria.Role == "Select") //if no role is selected
              SqlAda.SelectCommand.CommandText = "select (UD.UD_FirstName+' '+IsNull(UD.UD_MiddleName,'')+' '+IsNull(UD.UD_LastName,'')) as EmployeeName,UD.*,UL.* from UserDetails as UD LEFT OUTER JOIN UserList as UL ON " +
              "UD.UD_UserPersonalID=UL.UL_UserPersonalID WHERE UD.UD_MiddleName LIKE '" + '%' + "' + '" + criteria.CriteriaName + "' + '" + '%' + "' " +
              "or UD.UD_LastName LIKE  + '" + '%' + "' + '" + criteria.CriteriaName + "' + '" + '%' + "' order by EmployeeName";
          else
              SqlAda.SelectCommand.CommandText = "SELECT     (UD.UD_FirstName+' '+IsNull(UD.UD_MiddleName,'')+' '+IsNull(UD.UD_LastName,'')) as EmployeeName,UD.*, UL.* FROM UserDetails AS UD JOIN UserRolesMap AS UR " +
         "ON UD.UD_UserPersonalID = UR.UM_UserID LEFT OUTER JOIN UserList AS UL ON UD.UD_UserPersonalID = UL.UL_UserPersonalID " +
         "WHERE UD.UD_MiddleName LIKE '" + '%' + "' + '" + criteria.CriteriaName + "' + '" + '%' + "' or UD.UD_LastName LIKE  " +
         " '" + '%' + "' + '" + criteria.CriteriaName + "' + '" + '%' + "' AND UR.UM_RoleID = '" + criteria.Role + "' order by EmployeeName";

      }
      else if (criteria.SetCriteria == "LoginID")
      {
          SqlAda.SelectCommand.CommandText = "select (UD.UD_FirstName+' '+IsNull(UD.UD_MiddleName,'')+' '+IsNull(UD.UD_LastName,'')) as EmployeeName,UD.*,UL.* from UserDetails as UD,UserList as UL where " +
        "UL.UL_UserName  LIKE '" + '%' + "' + '" + criteria.CriteriaName + "' + '" + '%' + "' and UD.UD_UserPersonalID=UL.UL_UserPersonalID order by EmployeeName";

      }
      else if (criteria.SetCriteria == "Date of Creation")
      {
          if (criteria.DateofCreation != string.Empty)
          {
              if (criteria.DateStatus == "On")
                  dateOfCreation = "US.US_CreatedDate = '" + criteria.DateofCreation + "'";

              else if (criteria.DateStatus == "Before")
                  dateOfCreation = "US.US_CreatedDate < '" + criteria.DateofCreation + "'";

              else if (criteria.DateStatus == "After")
                  dateOfCreation = "US.US_CreatedDate > '" + criteria.DateofCreation + "'";

              SqlAda.SelectCommand.CommandText = "select (UD.UD_FirstName+' '+IsNull(UD.UD_MiddleName,'')+' '+IsNull(UD.UD_LastName,'')) as EmployeeName,UD.*,UL.* from UserDetails as UD LEFT OUTER JOIN " +
                 "UserList as UL ON UD.UD_UserPersonalID=UL.UL_UserPersonalID join UserGroupMap as UR on   UD.UD_UserPersonalID=UR_UserID join UserGroup as US on UR.UR_UserGroupID=US.US_GroupID WHERE " +
                 "" + dateOfCreation + " order by EmployeeName";
          }
          else
              SqlAda.SelectCommand.CommandText = "select (UD.UD_FirstName+' '+IsNull(UD.UD_MiddleName,'')+' '+IsNull(UD.UD_LastName,'')) as EmployeeName,UD.*,UL.* from UserDetails as UD LEFT OUTER JOIN " +
              "UserList as UL ON UD.UD_UserPersonalID=UL.UL_UserPersonalID left outer join UserGroupMap as UR on   UD.UD_UserPersonalID=UR_UserID left outer join UserGroup as US on UR.UR_UserGroupID=US.US_GroupID order by EmployeeName";
        
      }
      else
      {         
          return false;
      }
      SqlAda.Fill(result);
      return true;
  }

    /// <summary>
    /// Sign up
    /// </summary>
    /// <param name="user"></param>
    /// <remarks>
    /// Registers the newly created user on to the system,by creating a unique login name and password,
    /// using which he can login.An authentication question and answer is provided for checking-in case 
    /// of password recovery,once the password is forgotten.
    /// </remarks>
    /// <returns>boolean</returns>
    public bool UserRegister(UserDetails user)
    {
        SqlAda.SelectCommand.CommandText = "select * from UserList  where UL_UserName = '" + user.UserFirstName + "' and UL_Password= '" + user.Password + "' ";       
        Ds.Clear();
        SqlAda.Fill(Ds);

        if (Ds.Tables[0].Rows.Count > 0)
        {
            return false;
        }
        else
        {
            Dr = Ds.Tables[0].NewRow();
            Dr["UL_UserPersonalID"] = user.UserID;
            Dr["UL_UserName"] = user.UserFirstName;
            Dr["UL_Password"] = user.Password;
            Dr["UL_AuthQuestion"] = user.AuthenticationQuestion;
            Dr["UL_AuthAnswer"] = user.AuthenticationAnswer;
            Ds.Tables[0].Rows.Add(Dr);
            SqlAda.Update(Ds);
            return true;
        }

        return true;
    }

    public bool SearchUserGroup(string toSearch, out DataSet result)
    {
        result = new DataSet();
        result.Clear();
        //SqlAda = new SqlDataAdapter("", _sqlcon);

        if (toSearch == "BlankSearch")
        {
            SqlAda.SelectCommand.CommandText = "select * from UserGroup";
        }
        else
        {
            SqlAda.SelectCommand.CommandText = "select * from UserGroup where US_GroupName like '" + '%' + "' + '" + toSearch + "' + '" + '%' + "' ";

        }
        if (toSearch == "Latest")
        {
            SqlAda.SelectCommand.CommandText = "select top 10 * from UserGroup";
        }

        SqlAda.Fill(result);

        if (result.Tables[0].Rows.Count == 0)
        {
            return false;
        }
        return true;

    }


    public bool AddUserGroup(UserDetails UserGroup)
    {
        SqlAda.SelectCommand.CommandText = "select * from UserGroup where US_GroupName = '" + UserGroup.GroupName + "' ";
        Ds.Clear();
        SqlAda.Fill(Ds);
        if (Ds.Tables[0].Rows.Count > 0)
        {
            return false;
        }
        Dr = Ds.Tables[0].NewRow();
        Dr["US_GroupName"] = UserGroup.GroupName;
        Dr["US_Description"] = UserGroup.GroupDescription;
        Dr["US_CreatedBy"] = UserGroup.CreatedBy;
        if (UserGroup.DateOfCreation == string.Empty)
            Dr["US_CreatedDate"] = DBNull.Value;
        else
            Dr["US_CreatedDate"] = UserGroup.DateOfCreation;
        Ds.Tables[0].Rows.Add(Dr);
        SqlAda.Update(Ds);
        return true;
    }

   /// <summary>
   /// Edit User Group
   /// </summary>
   /// <param name="UserGroup"></param>
   /// <returns></returns>
    public bool EditUserGroup(UserDetails UserGroup)
    {
        Sqlcom.CommandText = "update UserGroup set US_GroupName='" + UserGroup.GroupName + "', " +
        "US_Description='" + UserGroup.GroupDescription + "',US_CreatedBy='" + UserGroup.CreatedBy + "', " +
        "US_CreatedDate = '" + UserGroup.DateOfCreation + "' where US_GroupID='" + UserGroup.GroupID + "'";
        if (UserGroup.DateOfCreation == string.Empty)
            Sqlcom.CommandText = "update UserGroup set US_GroupName='" + UserGroup.GroupName + "', " +
            "US_Description='" + UserGroup.GroupDescription + "',US_CreatedBy='" + UserGroup.CreatedBy + "' " +
            "where US_GroupID='" + UserGroup.GroupID + "'";
        _sqlcon.Open();
        Sqlcom.ExecuteNonQuery();
        _sqlcon.Close();
        return true;
    }

    /// <summary>
    /// 
    /// </summary>
    /// <param name="conditionDeleteUserGroupMap"></param>
    /// <param name="conditionDeleteUserGroup"></param>
    /// <returns></returns>
    public bool DeleteUserGroup(string conditionDeleteUserGroupMap, string conditionDeleteUserGroup)
    {
        Sqlcom.CommandText = "delete from UserGroupMap where " + conditionDeleteUserGroupMap + " ";
        _sqlcon.Open();
        Sqlcom.ExecuteNonQuery();
        _sqlcon.Close();

        Sqlcom.CommandText = "delete from UserGroup where " + conditionDeleteUserGroup + " ";
        _sqlcon.Open();
        Sqlcom.ExecuteNonQuery();
        _sqlcon.Close();

        return true;
    }

    /// <summary>
    /// Assign users to User Group
    /// </summary>
    /// <param name="GroupID"></param>
    /// <param name="userID"></param>
    /// <returns></returns>
    public bool UserGroupMap(string GroupID, string userID)
    {
        SqlAda.SelectCommand.CommandText = "select * from UserGroupMap where UR_UserID = '" + userID + "' and UR_UserGroupID = '" + GroupID + "' ";
        Ds.Clear();
        SqlAda.Fill(Ds);
        if (Ds.Tables[0].Rows.Count > 0)
        {
            return false;
        }
        Dr = Ds.Tables[0].NewRow();
        Dr["UR_UserID"] = userID;
        Dr["UR_UserGroupID"] = GroupID;
        Ds.Tables[0].Rows.Add(Dr);
        SqlAda.Update(Ds);
        return true;
    }

    /// <summary>
    /// Remove users from User Group
    /// </summary>
    /// <param name="condition"></param>
    /// <returns></returns>
    public bool RemoveUsersFromGroup(string condition)
    {
        Sqlcom.CommandText = "delete from UserGroupMap where " + condition + "";
        _sqlcon.Open();
        Sqlcom.ExecuteNonQuery();
        _sqlcon.Close();

        return true;
    }

    #endregion 

    #region "Role"

    /// <summary>
    /// add a role for users
    /// </summary>
    /// <param name="roleName"></param>
    /// <remarks>
    /// Each user is given a particular role.This role determines what activities he is alllowed to perform
    /// in the system.
    /// </remarks>
    /// <returns></returns>
    public bool AddRole(string roleName,string description)
    {
        SqlAda.SelectCommand.CommandText = "select * from UserRolesList where UR_RoleName = '" + roleName + "' ";
        SqlAda.Fill(Ds);
        if (Ds.Tables[0].Rows.Count > 0)
        {
            return false;
        }
        Dr = Ds.Tables[0].NewRow();
        Dr["UR_RoleName"] = roleName;
        Dr["UR_RoleDescription"] = description;
        Ds.Tables[0].Rows.Add(Dr);
        SqlAda.Update(Ds);
        return true;
    }

    /// <summary>
    /// Search a role 
    /// </summary>
    /// <param name="toSearch"></param>
    /// <param name="result"></param>
    /// <returns></returns>
    public bool RoleSearch(string toSearch, out DataSet result)
    {
        result = new DataSet();
        result.Clear();
       
        if (toSearch == "Latest")

            SqlAda.SelectCommand.CommandText = "select top 10 * from UserRolesList order by UR_RoleID desc";

        else
            SqlAda.SelectCommand.CommandText = "select * from UserRolesList where UR_RoleName like '" + '%' + "' + '" + toSearch + "' + '" + '%' + "' order by UR_RoleName";

        SqlAda.Fill(result);

        if (result.Tables[0].Rows.Count == 0)
        {
            return false;
        }
        return true;
        
    }

    /// <summary>
    /// Edit an existing role.
    /// </summary>
    /// <param name="role"></param>
    /// <returns></returns>
    public bool RoleEdit(UserRoles role)
    {
        Sqlcom.CommandText = "update UserRolesList set UR_RoleName='" + role.RoleName + "',UR_RoleDescription='" + role.RoleDescription + "' where UR_RoleID='" + role.RoleID + "'";
        _sqlcon.Open();
        Sqlcom.ExecuteNonQuery();
        _sqlcon.Close();
        return true;
    }
    public bool RemoveUserRole(string condition)
    {
        Sqlcom.CommandText = "delete from UserRolesMap where " + condition + "";
        _sqlcon.Open();
        Sqlcom.ExecuteNonQuery();
        _sqlcon.Close();

        return true;
    }
    

    public bool RoleDelete(string conditionDeleteRoleMap, string conditionDeletePermissionMap, string conditionDeleteRole)
    {
        Sqlcom.CommandText = "delete from UserRolesMap  where " + conditionDeleteRoleMap + " ";
        _sqlcon.Open();
        Sqlcom.ExecuteNonQuery();
        _sqlcon.Close();

        Sqlcom.CommandText = "delete from PermissionsMap  where " + conditionDeletePermissionMap + " ";
        _sqlcon.Open();
        Sqlcom.ExecuteNonQuery();
        _sqlcon.Close();

        Sqlcom.CommandText = "delete from UserRolesList  where " + conditionDeleteRole + " ";
        _sqlcon.Open();
        Sqlcom.ExecuteNonQuery();
        _sqlcon.Close();
        return true;
    }


    #endregion

    #region "Permission"



    /// <summary>
    /// Search for a permission
    /// </summary>
    /// <param name="toSearch"></param>
    /// <param name="result"></param>
    /// <returns></returns>
    public bool PermissionSearch(string toSearch, out DataSet result)
    {
        result = new DataSet();
        result.Clear();
        
        if (toSearch == "Latest")

            SqlAda.SelectCommand.CommandText = "select top 10 * from PermissionList order by PL_PermissionID desc";

        else
            SqlAda.SelectCommand.CommandText = "select * from PermissionList where PL_PermissionName like '" + '%' + "' + '" + toSearch + "' + '" + '%' + "' order by PL_PermissionName";


        SqlAda.Fill(result);

        if (result.Tables[0].Rows.Count == 0)
        {
            return false;
        }
        return true;

    }



    /// <summary>
    /// Add a Permission
    /// </summary>
    /// <param name="permissionName"></param>
    /// <param name="customID"></param>
    /// <remarks>
    /// Users as well as usergroups are given permissions according to which their actions on various
    /// utilities are determined.
    /// </remarks>
    /// <returns></returns>
    public bool AddPermission(string permissionName, string customID)
    {
        SqlAda.SelectCommand.CommandText = "select * from PermissionList where PL_PermissionName = '" + permissionName + "' ";
        SqlAda.Fill(Ds);
        if (Ds.Tables[0].Rows.Count > 0)
        {
            return false;
        }
        Dr = Ds.Tables[0].NewRow();
        Dr["PL_PermissionName"] = permissionName;
        if (customID == string.Empty)
            Dr["PL_CustomID"] = DBNull.Value;
        else
            Dr["PL_CustomID"] = customID;
        Ds.Tables[0].Rows.Add(Dr);
        SqlAda.Update(Ds);
        return true;
    }


   


    /// <summary>
    /// Edit Permissions
    /// </summary>
    /// <param name="permission"></param>
    /// <returns></returns>
    public bool EditPermission(UserPermission  permission)
    {
        if (permission.CustomID == string.Empty)
            Sqlcom.CommandText = "update PermissionList set PL_PermissionName='" + permission.PermissionName + "',PL_CustomID='" + null + "' where PL_PermissionID='" + permission.PermissionID + "'";
        else
            Sqlcom.CommandText = "update PermissionList set PL_PermissionName='" + permission.PermissionName + "',PL_CustomID='" + permission.CustomID + "' where PL_PermissionID='" + permission.PermissionID + "'";
        _sqlcon.Open();
        Sqlcom.ExecuteNonQuery();
        _sqlcon.Close();
        return true;
    }

    /// <summary>
    /// Delete Permissions
    /// </summary>
    /// <param name="permissionID"></param>
    /// <returns></returns>
    public bool PermissionDelete(string conditionDeletePermission,string conditionDeletePermissionMap)
    {
        Sqlcom.CommandText = "delete from PermissionsMap  where " + conditionDeletePermissionMap + " ";
        _sqlcon.Open();
        Sqlcom.ExecuteNonQuery();
        _sqlcon.Close();

        Sqlcom.CommandText = "delete from PermissionList  where " + conditionDeletePermission + " ";
        _sqlcon.Open();
        Sqlcom.ExecuteNonQuery();
        _sqlcon.Close();
        return true;
    }

    #endregion

    #region "Utility"

    /// <summary>
    /// Search a utility
    /// </summary>
    /// <param name="toSearch"></param>
    /// <param name="result"></param>
    /// <returns></returns>
    public bool SearchUtility(string toSearch, out DataSet result)
    {
        result = new DataSet();
        result.Clear();
       
        if (toSearch == "BlankSearch")
        {
            SqlAda.SelectCommand.CommandText = "select UT.*,UG.* from UtilityList as UT left outer join UtilityGroup as UG on UT.UT_UtilityGroupID=UG.UG_GroupID";
        }
        else
        {
            SqlAda.SelectCommand.CommandText = "select UT.*,UG.* from UtilityList as UT left outer join UtilityGroup as UG on UT.UT_UtilityGroupID=UG.UG_GroupID where UT.UT_UtilityName like '" + '%' + "' + '" + toSearch + "' + '" + '%' + "' ";

        }
        if (toSearch == "Latest")
        {
            SqlAda.SelectCommand.CommandText = "select top 10 UT.*,UG.* from UtilityList as UT left outer join UtilityGroup as UG on UT.UT_UtilityGroupID=UG.UG_GroupID";
        }
       
        SqlAda.Fill(result);

        if (result.Tables[0].Rows.Count == 0)
        {
            return false;
        }
        return true;

    }

    /// <summary>
    /// Add new utility
    /// </summary>
    /// <param name="addUtility"></param>
    /// <returns></returns>
    public bool AddUtility(Utility addUtility)
    {
        SqlAda.SelectCommand.CommandText = "select * from UtilityList where UT_UtilityName = '" + addUtility.UtilityName + "' ";
        SqlAda.Fill(Ds);
        if (Ds.Tables[0].Rows.Count > 0)
        {
            return false;
        }
        Dr = Ds.Tables[0].NewRow();
        Dr["UT_UtilityName"] = addUtility.UtilityName;
        if (addUtility.GroupID != string.Empty)
            Dr["UT_UtilityGroupID"] = addUtility.GroupID;
        Ds.Tables[0].Rows.Add(Dr);
        SqlAda.Update(Ds);
        return true;
    }

    /// <summary>
    /// Edit existing utility
    /// </summary>
    /// <param name="newUtility"></param>
    /// <returns></returns>
    public bool EditUtility(Utility newUtility)
    {
        if (newUtility.GroupID != string.Empty)
            Sqlcom.CommandText = "update UtilityList set UT_UtilityName='" + newUtility.UtilityName + "',UT_UtilityGroupID='" + newUtility.GroupID + "' where UT_UtilityID='" + newUtility.UtilityID + "'";
        else
            Sqlcom.CommandText = "update UtilityList set UT_UtilityName='" + newUtility.UtilityName + "' where UT_UtilityID='" + newUtility.UtilityID + "'";
        _sqlcon.Open();
        Sqlcom.ExecuteNonQuery();
        _sqlcon.Close();
        return true;
    }

    /// <summary>
    /// Delete utility
    /// </summary>
    /// <param name="utilityID"></param>
    /// <returns></returns>
    public bool DeleteUtility(string conditionDeletePermissionMap, string conditionDeleteUtility)
    {
        Sqlcom.CommandText = "delete from PermissionsMap where " + conditionDeletePermissionMap + " ";
        _sqlcon.Open();
        Sqlcom.ExecuteNonQuery();
        _sqlcon.Close();

        Sqlcom.CommandText = "delete from UtilityList where " + conditionDeleteUtility + " ";
        _sqlcon.Open();
        Sqlcom.ExecuteNonQuery();
        _sqlcon.Close();
        return true;
    }

    public bool SearchUtilityGroup(string toSearch, out DataSet result)
    {
        result = new DataSet();
        result.Clear();
        
        if (toSearch == "BlankSearch")
        {
            SqlAda.SelectCommand.CommandText = "select * from UtilityGroup";
        }
        else
        {
            SqlAda.SelectCommand.CommandText = "select * from UtilityGroup where UG_GroupName like '" + '%' + "' + '" + toSearch + "' + '" + '%' + "' ";

        }
        if (toSearch == "Latest")
        {
            SqlAda.SelectCommand.CommandText = "select top 10 * from UtilityGroup";
        }

        SqlAda.Fill(result);

        if (result.Tables[0].Rows.Count == 0)
        {
            return false;
        }
        return true;

    }

    /// <summary>
    /// Add new utility group
    /// </summary>
    /// <param name="newUtilityGroup"></param>
    /// <returns></returns>
    public bool AddUtilityGroup(Utility newUtilityGroup)
    {
        SqlAda.SelectCommand.CommandText = "select * from UtilityGroup where UG_GroupName = '" + newUtilityGroup.GroupName + "' ";
        SqlAda.Fill(Ds);
        if (Ds.Tables[0].Rows.Count > 0)
        {
            return false;
        }
        Dr = Ds.Tables[0].NewRow();
        Dr["UG_GroupName"] = newUtilityGroup.GroupName;
        Dr["UG_Description"] = newUtilityGroup.GroupDescription;
        Dr["UG_CreatedBy"] = newUtilityGroup.CreatedBy;
        if (newUtilityGroup.DateOfCreation == string.Empty)
            Dr["UG_CreatedDate"] = DBNull.Value;
        else
            Dr["UG_CreatedDate"] = newUtilityGroup.DateOfCreation;
        Ds.Tables[0].Rows.Add(Dr);
        SqlAda.Update(Ds);
        return true;
    }

    /// <summary>
    /// 
    /// </summary>
    /// <param name="utilityGroup"></param>
    /// <returns></returns>
    public bool EditUtilityGroup(Utility utilityGroup)
    {
        Sqlcom.CommandText = "update UtilityGroup set UG_GroupName='" + utilityGroup.GroupName + "', " +
             "UG_Description='" + utilityGroup.GroupDescription + "',UG_CreatedBy='" + utilityGroup.CreatedBy + "', " +
             "UG_CreatedDate = '" + utilityGroup.DateOfCreation + "' where UG_GroupID='" + utilityGroup.GroupID + "'";
        if (utilityGroup.DateOfCreation == string.Empty)
            Sqlcom.CommandText = "update UtilityGroup set UG_GroupName='" + utilityGroup.GroupName + "', " +
            "UG_Description='" + utilityGroup.GroupDescription + "',UG_CreatedBy='" + utilityGroup.CreatedBy + "' " +
            "where UG_GroupID='" + utilityGroup.GroupID + "'";
        _sqlcon.Open();
        Sqlcom.ExecuteNonQuery();
        _sqlcon.Close();
        return true;
    }

    /// <summary>
    /// 
    /// </summary>
    /// <param name="newUtilityGroup"></param>
    /// <returns></returns>
    public bool DeleteUtilityGroup(string conditionUpdateUtilityList, string conditionDeleteUtilityGroup)
    {
        Sqlcom.CommandText = "update UtilityList set UT_UtilityGroupID = null where " + conditionUpdateUtilityList + " ";
        _sqlcon.Open();
        Sqlcom.ExecuteNonQuery();
        _sqlcon.Close();

        Sqlcom.CommandText = "delete from UtilityGroup where " + conditionDeleteUtilityGroup + " ";
        _sqlcon.Open();
        Sqlcom.ExecuteNonQuery();
        _sqlcon.Close();

        return true;
    }

    /// <summary>
    /// Add/Remove Utility from utility group.
    /// </summary>
    /// <param name="condition"></param>
    /// <param name="groupID"></param>
    /// <returns></returns>
    public bool AddRemoveUtility(string condition, string groupID)
    {
        string groupUpdate;
        if (groupID == string.Empty)
            groupUpdate = "UT_UtilityGroupID= null  ";
        else
            groupUpdate = "UT_UtilityGroupID=" + groupID + "";

        //_sqlcom = new SqlCommand("", _sqlcon);
        Sqlcom.CommandText = "update UtilityList set " + groupUpdate + " where " + condition + "";

        _sqlcon.Open();
        Sqlcom.ExecuteNonQuery();
        _sqlcon.Close();

        return true;
    }

    #endregion

    #region "User - Roles"

    /// <summary>
    /// Search a role assigned to user.
    /// </summary>
    /// <param name="toSearch"></param>
    /// <param name="result"></param>
    /// <remarks>
    /// Once a user name is entered,his details and his role are listed together.
    /// </remarks>
    /// <returns></returns>
    public bool SearchUserRole(string toSearch, out DataSet result)
    {
        result = new DataSet();
        try
        {           
            SqlAda.SelectCommand.CommandText = "select UD.UD_FirstName as UserName,UR.UR_RoleName from UserDetails as UD,UserRolesList as UR,UserRolesMap as UM where " +
            "UD.UD_FirstName like '" + toSearch.Trim() + "' + '" + '%' + "' and UD.UD_UserPersonalID=UM.UM_UserID and UM.UM_RoleID =UR.UR_RoleID";
            SqlAda.Fill(result);
            return true;
        }
        catch (Exception ex)
        {
            return false;
        }
    }

    /// <summary>
    /// Assign role to a user.
    /// </summary>
    /// <param name="userID"></param>
    /// <param name="RoleID"></param>
    /// <returns></returns>
    public bool AssignRole(string userID,string RoleID)
    {
        SqlAda.SelectCommand.CommandText = "select * from UserRolesMap where UM_UserID = '" + userID + "' and UM_RoleID = '" + RoleID + "' ";
        Ds.Clear();
        SqlAda.Fill(Ds);
        if (Ds.Tables[0].Rows.Count > 0)
        {
            return false;
        }
        Dr = Ds.Tables[0].NewRow();
        Dr["UM_UserID"] = userID;
        Dr["UM_RoleID"] = RoleID;
        Ds.Tables[0].Rows.Add(Dr);
        SqlAda.Update(Ds);
        return true;
    }

    /// <summary>
    /// Modify an already assigned role.
    /// </summary>
    /// <param name="userID"></param>
    /// <param name="roleID"></param>
    /// <returns></returns>
    public bool ModifyUserRole(string userID,string roleID)
    {
        Sqlcom.CommandText = "update UserRolesMap set UM_RoleID='" + roleID + "' where UM_UserID='" + userID + "'";
        _sqlcon.Open();
        Sqlcom.ExecuteNonQuery();
        _sqlcon.Close();
        return true;
    }

    /// <summary>
    /// Delete a user-role assignment.
    /// </summary>
    /// <param name="userID"></param>
    /// <returns></returns>
    public bool DeleteUserRole(string userID)
    {
        Sqlcom.CommandText = "delete from UserRolesMap where UM_UserID= '" + userID + "'";
        _sqlcon.Open();
        Sqlcom.ExecuteNonQuery();
        _sqlcon.Close();

        return true;
    }

    #endregion

    #region "User/Role - Permissions"


    public bool SearchRolePermission(string ID, string groupID, out DataSet result)
    {
        result = new DataSet();
        try
        {
            string group = string.Empty;
            if (groupID != string.Empty)
                group = "and UG.UG_GroupID = '" + groupID + "'";

            SqlAda.SelectCommand.CommandText = "SELECT DISTINCT UT.UT_UtilityID, UT.UT_UtilityName,UG.UG_GroupID, " +
            "UG.UG_GroupName FROM UtilityList AS UT, PermissionsMap AS PM, UtilityGroup AS UG WHERE UT.UT_UtilityID = PM.PM_UtilityID " +
            "AND  UT.UT_UtilityGroupID = UG.UG_GroupID AND PM.PM_ActorID = '" + ID + "' " + group + " ";
            SqlAda.Fill(result);
            return true;
        }
        catch (Exception ex)
        {
            return false;
        }

    }


    public bool SearchUsersRoles(string type, string toSearch, out DataSet result)
    {
        result = new DataSet();
        if (type == "1")
        {
            if (toSearch == "Latest")
                SqlAda.SelectCommand.CommandText = "select top 10 UD_UserPersonalID as ID,(UD_FirstName+' '+IsNull(UD_MiddleName,'')+' '+IsNull(UD_LastName,'')) as Name from UserDetails";
            else
                SqlAda.SelectCommand.CommandText = "select UD_UserPersonalID as ID,(UD_FirstName+' '+IsNull(UD_MiddleName,'')+' '+IsNull(UD_LastName,'')) as Name from UserDetails where " +
                "UD_FirstName like '" + '%' + "' + '" + toSearch + "' + '" + '%' + "' order by UD_FirstName";
        }
        else
        {
           
            if (toSearch == "Latest")
                SqlAda.SelectCommand.CommandText = "select top 10 UR_RoleID as ID,UR_RoleName as Name from UserRolesList ";
            else
                SqlAda.SelectCommand.CommandText = "select UR_RoleID as ID,UR_RoleName as Name from UserRolesList  where " +
                "UR_RoleName like '" + '%' + "' + '" + toSearch + "' + '" + '%' + "' order by UR_RoleName";
        }
        SqlAda.Fill(result);
        return true;

    }


    /// <summary>
/// Search a user/group-permission
/// </summary>
/// <param name="type"></param>
/// <param name="toSearch"></param>
/// <param name="result"></param>
/// <returns></returns>
    public bool SearchActorPermission(string type,string toSearch, out DataSet result)
    {
        result = new DataSet();

        if (type == "1")
            SqlAda.SelectCommand.CommandText = "select UT.UT_UtilityName,PL.PL_PermissionName,PM.PM_PMID " +
            "from UserDetails as UD join PermissionsMap as PM on UD.UD_UserPersonalID=PM.PM_ActorID left outer join UtilityList " +
            "as UT on PM.PM_UtilityID = UT.UT_UtilityID left outer join PermissionList as PL on PM.PM_PermissionID = PL.PL_PermissionID " +
            "where UD.UD_UserPersonalID = '" + toSearch + "'";
        else
        {
            SqlAda.SelectCommand.CommandText = "select UT.UT_UtilityName,PL.PL_PermissionName,PM.PM_PMID " +
            "from UserRolesList as UR join PermissionsMap as PM on UR.UR_RoleID=PM.PM_ActorID left outer join UtilityList " +
            "as UT on PM.PM_UtilityID = UT.UT_UtilityID left outer join PermissionList as PL on PM.PM_PermissionID = PL.PL_PermissionID " +
            "where UR.UR_RoleID = '" + toSearch + "'";
        }

        SqlAda.Fill(result);
        if (result.Tables[0].Rows.Count == 0)
            return false;
        else
            return true;

    }

    /// <summary>
    /// Assign a permission to user for utility/group
    /// </summary>
    /// <param name="permission"></param>
    /// <returns></returns>
    public bool AssignPermission(UserPermission permission)
    {
        SqlAda.SelectCommand.CommandText = "select * from PermissionsMap where PM_ActorID = '" + permission.ActorID + "' " +
       "and PM_PermissionID = '" + permission.PermissionID + "' and PM_UtilityID = '" + permission.UtilityID + "'";
        Ds.Clear();
        SqlAda.Fill(Ds);
        if (Ds.Tables[0].Rows.Count > 0)
        {
            return false;
        }
        Dr = Ds.Tables[0].NewRow();
        Dr["PM_ActorType"] = permission.ActorType;
        Dr["PM_ActorID"] = permission.ActorID;
        Dr["PM_PermissionID"] = permission.PermissionID;
        Dr["PM_UtilityID"] = permission.UtilityID;
        Ds.Tables[0].Rows.Add(Dr);
        SqlAda.Update(Ds);
        return true;
    }

    public bool RemovePermission(string condition)
    {
        Sqlcom.CommandText = "delete from PermissionsMap where " + condition + " ";
        _sqlcon.Open();
        Sqlcom.ExecuteNonQuery();
        _sqlcon.Close();

        return true;
    }

    #endregion

    #region Image
    public void ImageUpload(byte[] picture, string userID)
    {
        //_sqlcom = new SqlCommand("", _sqlcon);       
        Sqlcom.CommandText = "update UserDetails set UD_Photo = @picture where UD_UserPersonalID = '" + userID + "'";
        Sqlcom.Parameters.Add("@picture", picture);
        _sqlcon.Open();
        Sqlcom.ExecuteNonQuery();
        _sqlcon.Close();
    }

    public void ImageLoad(string userID,out DataSet result)
    {
        result = new DataSet();      
        SqlAda.SelectCommand.CommandText = "SELECT UD_Photo FROM UserDetails where UD_UserPersonalID=" + userID + "";
        SqlAda.Fill(result);
                   
    }

    #endregion 

    //public void AddUser()
    //{
    //    throw new NotImplementedException();
    //}

    //public void ImageUpload()
    //{
    //    throw new NotImplementedException();
    //}
}
}
